using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class LoaiPhongDAO
    {
        private LoaiPhongDTO[] ConvertToLoaiPhongDTO(DataTable dt)
        {
            int n = dt.Rows.Count;
            LoaiPhongDTO[] Arr = new LoaiPhongDTO[n];
            for (int i = 0; i < n; i++)
            {
                Arr[i] = new LoaiPhongDTO();
                Arr[i].ID_LP = Convert.ToInt32(dt.Rows[i]["ID_LP"].ToString());
                Arr[i].MaLP = dt.Rows[i]["MaLP"].ToString();
                Arr[i].TenLP = dt.Rows[i]["TenLP"].ToString();
                Arr[i].DonGia = Convert.ToInt64(dt.Rows[i]["DonGia"]);
                Arr[i].MoTa = dt.Rows[i]["MoTa"].ToString();
            }
            return Arr;
        }
        public LoaiPhongDTO[] TimKiemLoaiPhong(int ID, string MaLP, string TenLP, Double DonGia, string MoTa)
        {
            LoaiPhongDTO[] Arr = new LoaiPhongDTO[100];
            try
            {
                LoaiPhongDTO lpDTO = new LoaiPhongDTO();
                lpDTO.ID_LP = ID;
                lpDTO.MaLP = MaLP;
                lpDTO.TenLP = TenLP;
                lpDTO.DonGia = DonGia;
                lpDTO.MoTa = MoTa;
                Arr = TimKiemLoaiPhongDTO(lpDTO);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return Arr;
        }

        private LoaiPhongDTO[] TimKiemLoaiPhongDTO(LoaiPhongDTO lpDTO)
        {
            LoaiPhongDTO[] Arr = new LoaiPhongDTO[100];
            try
            {                
                DataTable dt = new DataTable();
                string strID = " 1 = 1 ";
                string strMaLP = " 1 = 1 ";
                string strTenLP = " 1 = 1 ";
                string strDonGia = " 1 = 1 ";
                string strMoTa = " 1 = 1 ";

                if (lpDTO.ID_LP != 0)
                    strID = " ID_LP = " + lpDTO.ID_LP;
                if (string.Compare(lpDTO.MaLP, "") != 0)
                    strMaLP = " MaLP like N'%" + lpDTO.MaLP + "%'";
                if (string.Compare(lpDTO.TenLP, "") != 0)
                    strTenLP = " TenLP like N'%" + lpDTO.TenLP + "%'";
                if (lpDTO.DonGia != 0)
                    strDonGia = " DonGia = " + lpDTO.DonGia;
                if (string.Compare(lpDTO.MoTa, "") != 0)
                    strMoTa = " MoTa like N'%" + lpDTO.MoTa + "%'";

                DataProvider dp = new DataProvider();
                SqlConnection con = new SqlConnection();
                con = dp.ConnectionData();
                con.Open();

                string strSQL = "SELECT * FROM LOAIPHONG WHERE ";
                strSQL += strID + " AND ";
                strSQL += strMaLP + " AND ";
                strSQL += strTenLP + " AND ";
                strSQL += strDonGia + " AND ";
                strSQL += strMoTa;

                SqlCommand cmd = new SqlCommand(strSQL, con);
                cmd.ExecuteNonQuery();

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);

                Arr = ConvertToLoaiPhongDTO(dt);

                con.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return Arr;
        }

        public int ThemLoaiPhong(int ID, string MaLP, string TenLP, long DonGia, string MoTa)
        {
            int kq = 0;
            try
            {
                LoaiPhongDTO lpDTO = new LoaiPhongDTO();
                lpDTO.ID_LP = ID;
                lpDTO.MaLP = MaLP;
                lpDTO.TenLP = TenLP;
                lpDTO.DonGia = DonGia;
                lpDTO.MoTa = MoTa;
                kq = ThemLoaiPhongDTO(lpDTO);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return kq;
        }

        private int ThemLoaiPhongDTO(LoaiPhongDTO lpDTO)
        {
            try
            {
                SqlConnection con = new SqlConnection();
                DataProvider dp = new DataProvider();
                con = dp.ConnectionData();
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_ThemLoaiPhong";

                cmd.Parameters.Add("@MaLP", SqlDbType.Char);
                cmd.Parameters.Add("@TenLP", SqlDbType.NVarChar);
                cmd.Parameters.Add("@DonGia", SqlDbType.Money);
                cmd.Parameters.Add("@MoTa", SqlDbType.NText);
                cmd.Parameters.Add("@ID", SqlDbType.Int);

                cmd.Parameters["@MaLP"].Value = lpDTO.MaLP;
                cmd.Parameters["@TenLP"].Value = lpDTO.TenLP;
                cmd.Parameters["@DonGia"].Value = lpDTO.DonGia;
                cmd.Parameters["@MoTa"].Value = lpDTO.MoTa;
                cmd.Parameters["@ID"].Direction = ParameterDirection.Output;

                cmd.ExecuteNonQuery();
                lpDTO.ID_LP = Convert.ToInt32(cmd.Parameters["@ID"].Value.ToString());

                con.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return lpDTO.ID_LP;
        }

        public LoaiPhongDTO[] LayDanhSachLoaiPhong()
        {
            LoaiPhongDTO[] Arr = new LoaiPhongDTO[100];
            try
            {                
                DataTable dt = new DataTable();
                SqlConnection con = new SqlConnection();
                DataProvider dp = new DataProvider();
                con = dp.ConnectionData();
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_DanhSachLoaiPhong";

                cmd.ExecuteNonQuery();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                Arr = ConvertToLoaiPhongDTO(dt);

                con.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return Arr;
        }

        public void CapNhatLoaiPhong(int ID, string MaLP, string TenLP, long DonGia, string MoTa)
        {
            try
            {
                SqlConnection con = new SqlConnection();
                DataProvider dp = new DataProvider();
                con = dp.ConnectionData();
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_CapNhatLoaiPhong";

                cmd.Parameters.Add("@ID", SqlDbType.Int);
                cmd.Parameters.Add("@MaLp", SqlDbType.Char);
                cmd.Parameters.Add("@TenLP", SqlDbType.NVarChar);
                cmd.Parameters.Add("@DonGia", SqlDbType.Money);
                cmd.Parameters.Add("@MoTa", SqlDbType.NText);

                cmd.Parameters["@ID"].Value = ID;
                cmd.Parameters["@MaLP"].Value = MaLP;
                cmd.Parameters["@TenLP"].Value = TenLP;
                cmd.Parameters["@DonGia"].Value = DonGia;
                cmd.Parameters["@MoTa"].Value = MoTa;

                cmd.ExecuteNonQuery();

                con.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        public LoaiPhongDTO LayLoaiPhongTheoMaPhong(int MaPhong)
        {
            LoaiPhongDTO lpDTO = new LoaiPhongDTO();
            try
            {
                DataTable dt = new DataTable();
                SqlConnection con = new SqlConnection();
                DataProvider dp = new DataProvider();
                con = dp.ConnectionData();
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_LayLoaiPhongTheoMaPhong";

                cmd.Parameters.Add("@MaPhong", SqlDbType.Int);
                cmd.Parameters["@MaPhong"].Value = MaPhong;

                cmd.ExecuteNonQuery();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                lpDTO.ID_LP = Convert.ToInt32(dt.Rows[0]["ID_LP"].ToString());
                lpDTO.MaLP = dt.Rows[0]["MaLP"].ToString();
                lpDTO.TenLP = dt.Rows[0]["TenLP"].ToString();
                lpDTO.DonGia = Convert.ToInt64(dt.Rows[0]["DonGia"]);
                lpDTO.MoTa = dt.Rows[0]["MoTa"].ToString();

                con.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lpDTO;
        }
    }
}
